﻿Imports Bengen.SP.ImportDataTImer.Extensions.SPListItemExensions
Imports Bengen.SP.ImportDataTimer.Extensions.SPSiteExtensions
Imports Bengen.SP.ImportDataTimer.Extensions.SPListExtensions

Public Class Job

#Region "Private Variables"

    Private _startClock As DateTime
    Private _finalDuration As String
    Private _destinationSpList As SPList

#End Region

#Region "Properties"

    Private _jobName As String
    Public ReadOnly Property JobName As String
        Get
            Return _jobName
        End Get
    End Property

    Private _destinationList As String
    Public ReadOnly Property DestinationList As String
        Get
            Return _destinationList
        End Get
    End Property

    Private _providerType As String
    Public ReadOnly Property ProviderType As String
        Get
            Return _providerType
        End Get
    End Property

    Private _connectionString As String
    Public ReadOnly Property ConnectionString As String
        Get
            Return _connectionString
        End Get
    End Property

    Private _sql As String
    Public ReadOnly Property SQL As String
        Get
            Return _sql
        End Get
    End Property

    Private _recurrence As String
    Public ReadOnly Property Recurrence As String
        Get
            Return _recurrence
        End Get
    End Property

    Private _eventLogging As LogLevel
    Public ReadOnly Property EventLogging As LogLevel
        Get
            If Me.IsTestMode Then
                Me._eventLogging = LogLevel.Verbose
            End If
            Return _eventLogging
        End Get
    End Property

    Private _status As String
    Public ReadOnly Property Status As String
        Get
            Return _status
        End Get
    End Property

    Private _retention As Integer
    Public ReadOnly Property Retention As Integer
        Get
            Return _retention
        End Get
    End Property

    Public ReadOnly Property IsTestMode As Boolean
        Get
            Return _status = "Test"
        End Get
    End Property

    Private _item As SPListItem
    Public ReadOnly Property Item As SPListItem
        Get
            Return _item
        End Get
    End Property

    Private _log As Logger
    Public ReadOnly Property Log As Logger
        Get
            If _log Is Nothing Then
                _log = New Logger(Me.Item.ParentList.ParentWeb, Me.JobName, Me.EventLogging, Me.Retention)
            End If
            Return _log
        End Get
    End Property

#End Region

    ''' <summary>
    ''' Initializes a new instance of Job.
    ''' </summary>
    ''' <param name="item"></param>
    ''' <remarks>An error will be thrown if Job is not formatted correctly.</remarks>
    Public Sub New(ByRef item As SPListItem)
        Me._startClock = DateTime.Now
        Dim initStep As String = Nothing
        Try
            Me._item = item
            initStep = "Retrieving job name."
            Me._jobName = item.TryGetValue(Of String)(SPBuiltInFieldId.Title)
            initStep = "Retrieving destination list."
            Me._destinationList = item.TryGetValue(Of String)("Destination List")
            Me._destinationSpList = item.Web.Site.TryFindList(Me._destinationList)
            initStep = "Retrieving provider type."
            Me._providerType = item("Provider Type").ToString
            initStep = "Retrieving connection string."
            Me._connectionString = item("Connection String").ToString
            initStep = "Retrieving SQL."
            Me._sql = item("SQL").ToString
            initStep = "Retrieving recurrence."
            Me._recurrence = item("Recurrence").ToString
            initStep = "Retrieving event logging."
            Me._eventLogging = CType([Enum].Parse(GetType(LogLevel), item("Event Logging").ToString), LogLevel)
            initStep = "Retrieving status."
            Me._status = item("Status").ToString
            initStep = "Retrieving event retention."
            Me._retention = CInt(item("Event Retention").ToString)
        Catch ex As Exception
            Me.SetStatus("Failed", "Failed to initialize job.  " + initStep)
            Throw ex
        End Try
    End Sub



    Public Sub DoJob()
        Dim dt As DataTable = Nothing

        Using Me.Log
            Try
                If IsTestMode Then
                    Me.SetTestModeMessage()
                End If

                Log.AddHistory(LogLevel.VerboseDebug, "Timer version number (" + My.Application.Info.Version.ToString + ")")
                Log.AddHistory(LogLevel.Information, "Starting '" + Me.JobName + "' at " + Me._startClock.ToString + ".")

                Me.SetStatus("Running")

                Using site As New SPSite(Me.Item.Web.Site.Url)
                    Using web As SPWeb = site.OpenWeb(Me._destinationSpList.ParentWebUrl)
                        Log.AddHistory(LogLevel.Verbose, "Opened web '" + web.Url + "'.")
                        dt = Me.GetData
                        If dt.Rows.Count > 0 Then
                            Me.ValidateDestinationColumnsExist(dt)
                            Me.DoItems(dt, web)
                        End If
                    End Using
                End Using
                Log.AddHistory(LogLevel.Information, String.Format("Finished job '{0}' in {1}", Me.JobName, Me.GetFinalDuration))
            Catch ex As Exception
                Log.AddHistory(LogLevel.Critical, ex.Message)
                Me.SetStatus("Failed", ex.Message)
                Throw
            End Try

            Me.SetStatus("Success", String.Format("Imported {0} records in {1}.", dt.Rows.Count, Me.GetFinalDuration))
            Log.Close("Logger closed.")
        End Using
    End Sub

    Private Function GetData() As DataTable
        Dim dt As New DataTable
        Dim connectionTime As DateTime = DateTime.Now
        Log.AddHistory(LogLevel.Information, "Opening connection.")
        Using cn As IDbConnection = Me.GetConnection
            cn.Open()
            Using cm As IDbCommand = cn.CreateCommand
                cm.CommandText = Me.SQL
                Log.AddHistory(LogLevel.Verbose, String.Format("Creating command:{0}{1}", vbNewLine, cm.CommandText))
                Log.AddHistory(LogLevel.Information, "Reading data.")
                Using dr As IDataReader = cm.ExecuteReader
                    dt.Load(dr)
                End Using
            End Using
        End Using
        Dim ts As TimeSpan = DateTime.Now - connectionTime
        Log.AddHistory(LogLevel.Information, String.Format("Retrieved {0} records in {1}m {2}s", dt.Rows.Count, (ts.Days * 24 * 60) + (ts.Hours * 60) + ts.Minutes, ts.Seconds))

        Return dt
    End Function

    Private Sub ValidateDestinationColumnsExist(ByVal dt As DataTable)
        Dim isError As Boolean = False
        Dim flds As SPFieldCollection = _destinationSpList.Fields
        For Each col As DataColumn In dt.Columns
            If Not flds.ContainsFieldWithStaticName(_destinationSpList.Fields(col.ColumnName).StaticName) Then
                isError = True
                Log.AddHistory(LogLevel.Critical, "Query expects column with a display name of '" + col.ColumnName + "'.")
            End If
        Next

        If isError Then
            Throw New Exception("Destination list is missing column names.")
        Else
            Log.AddHistory(LogLevel.Information, "Destination list is validated and all columns exist.")
        End If
    End Sub

    Private Sub SetTestModeMessage()
        Dim sb As New Text.StringBuilder
        sb.Append("***** TEST MODE ******").AppendLine()
        sb.Append("***** Only one item will be added to the list. ******").AppendLine()
        sb.Append("***** The imported item will have a GUID for a title. ******").AppendLine()
        sb.Append("***** Event logging is set to VerboseDebug during TEST MODE *****").AppendLine()
        sb.Append("***** TEST MODE ******")
        Log.LogLevel = LogLevel.VerboseDebug
        Log.AddHistory(LogLevel.Verbose, sb.ToString)
    End Sub

    Private Function GetItem(ByVal dr As DataRow, ByVal containsUniqueField As Boolean, ByRef isNew As Boolean) As SPListItem

        If containsUniqueField Then
            Dim qs As String = "<Where><Eq><FieldRef Name='UniqueField'/><Value Type='Text'>" & dr("UniqueField").ToString & "</Value></Eq></Where>"
            Dim query As New SPQuery
            query.Query = qs
            Dim results As SPListItemCollection = Me._destinationSpList.GetItems(query)

            If results.Count > 0 Then
                'Log.AddHistory(LogLevel.Verbose, "Item exists: " + dr("UniqueField").ToString)
                If Me.IsTestMode Then
                    Log.AddHistory(LogLevel.Information, "While in TEST MODE the existing item will not be overwritten.  A new item will be created instead.")
                    Return Me._destinationSpList.Items.Add
                End If
                isNew = False
                Return results.Item(0)
            End If
        End If

        isNew = True
        'Log.AddHistory(LogLevel.Verbose, "Item does not exist.")
        Return Me._destinationSpList.Items.Add

    End Function

    Private Sub DoItems(ByVal dt As DataTable, ByVal web As SPWeb)

        Log.AddHistory(LogLevel.Information, String.Format("Adding/Updating data to '{0}'", _destinationSpList.Title))

        Dim newFieldIds As New List(Of String)
        Dim containsUniqueField As Boolean = dt.Columns.Contains("UniqueField")
        Dim itemsUpdated As Integer = 0
        Dim fieldsUpdated As Integer = 0
        Dim itemsAdded As Integer = 0

        Dim rowCount As Integer = 0
        If Not Me.IsTestMode Then
            rowCount = dt.Rows.Count - 1
        End If

        For index As Integer = 0 To rowCount
            'create new debug variable for current datarow
            '  this variable is used for VerboseDebug logging
            '  or if an error occurs during this process
            Dim debug As New Text.StringBuilder

            Try
                'declare new datarow variable
                Dim dr As DataRow = dt.Rows(index)
                'isNew is passed by reference to the GetItem method.
                Dim isNew As Boolean
                Dim item As SPListItem = Me.GetItem(dr, containsUniqueField, isNew)

                debug.Append(String.Format("Reading item: {0} of {1}", index + 1, rowCount + 1)).AppendLine()
                debug.Append(String.Format("Item is new: {0}", isNew)).AppendLine()

                'itemXml is used to capture changed fields and their new value
                '  itemXml is also used to determine if changes exist and whether or not call Item.Update
                Dim itemXml As XElement = <Item/>

                For Each col As DataColumn In dt.Columns
                    'get the SQL column title name
                    Dim sourceTitle As String = col.ColumnName
                    debug.Append(String.Format("Source Title is: '{0}'", sourceTitle))

                    Dim fld As SPField = item.Fields.GetFieldByInternalName(Me._destinationSpList.Fields(sourceTitle).InternalName)
                    debug.Append(String.Format(", Destination Title is: '{0}'.", fld.Title)).AppendLine()
                    If fld.Type = SPFieldType.User Then
                        ''find user
                        debug.Append("Field is user field...")
                        Dim u As SPUserCollection = web.AllUsers
                        Dim dbvalue As String = dr(col).ToString
                        Dim userfound As SPUser = Nothing
                        'First search for an exact user match
                        For Each user As SPUser In u
                            If user.Name.ToLower.Equals(dbvalue.ToLower) OrElse user.LoginName.ToLower.Equals(dbvalue.ToLower) OrElse user.ID.ToString = dbvalue Then
                                userfound = user
                                debug.Append(String.Format("Found user: '{0}'", user.ToString)).AppendLine()
                                Exit For
                            End If
                        Next
                        'if no exact user match was found, search for partial user match
                        If Not userfound Is Nothing Then
                            For Each user As SPUser In u
                                If user.Name.ToLower.StartsWith(dbvalue.ToLower) OrElse user.LoginName.ToLower.StartsWith(dbvalue.ToLower) OrElse user.ID.ToString = dbvalue Then
                                    userfound = user
                                    debug.Append(String.Format("Found user: '{0}'", user.ToString)).AppendLine()
                                    Exit For
                                End If
                            Next
                        End If
                        If userfound IsNot Nothing Then
                            debug.Append(String.Format("Source value is '{0}'", item.TryGetValue(Of String)(Me._destinationSpList.Fields(sourceTitle).InternalName)))
                            debug.Append(String.Format(", Existing value is: '{0}'", New SPFieldUserValue(web, userfound.ID, userfound.Name).ToString)).AppendLine()
                            'debug.Append(String.Format("Existing value: {0}, New Value: {1}", item.TryGetValue(Of String)(Me._destinationSpList.Fields(columnTitle).InternalName), New SPFieldUserValue(web, userfound.ID, userfound.Name).ToString)).AppendLine()
                            If isNew OrElse item.TryGetValue(Of String)(Me._destinationSpList.Fields(sourceTitle).InternalName) <> New SPFieldUserValue(web, userfound.ID, userfound.Name).ToString Then
                                Try
                                    itemXml.Add(New XElement(sourceTitle.Replace(" ", "_"), userfound.Name))
                                Catch ex As Exception
                                    itemXml.Add(New XElement("FailedUserColumnTitle", "FailedValue"))
                                End Try
                                debug.Append("Setting value.").AppendLine()
                                item(Me._destinationSpList.Fields(sourceTitle).InternalName) = New SPFieldUserValue(web, userfound.ID, userfound.Name)
                                If Not isNew Then fieldsUpdated += 1
                            End If
                        Else
                            Log.AddHistory(LogLevel.Warning, "Record number: " & index + 1 & "Could not find user:" & dbvalue & ".")
                        End If
                    Else
                        Dim sourceValue As String = If(dr.IsNull(col), "", dr(col).ToString)
                        Dim destValue As String = item.TryGetValue(Of String)(sourceTitle)
                        If destValue IsNot Nothing AndAlso IsDate(destValue) AndAlso IsDate(sourceValue) Then
                            Dim s As DateTime = CDate(sourceValue)
                            Dim d As DateTime = CDate(destValue)
                            debug.Append(String.Format("Source value is '{0}', ", s.ToString))
                            debug.Append(String.Format("Existing value is: '{0}'.", d.ToString)).AppendLine()
                            If isNew OrElse s <> d Then
                                Try
                                    itemXml.Add(New XElement(sourceTitle.Replace(" ", "_"), dr(col).ToString))
                                Catch ex As Exception
                                    itemXml.Add(New XElement("FailedColumnTitle", "FailedValue"))
                                End Try
                                debug.Append("Setting value.").AppendLine()
                                item(Me._destinationSpList.Fields(sourceTitle).InternalName) = dr(col).ToString
                                If Not isNew Then fieldsUpdated += 1
                            End If
                        Else
                            debug.Append(String.Format("Source value is '{0}', ", sourceValue))
                            debug.Append(String.Format("Existing value is: '{0}'.", item.TryGetValue(Of String)(sourceTitle))).AppendLine()
                            If isNew OrElse item.TryGetValue(Of String)(Me._destinationSpList.Fields(sourceTitle).InternalName) <> dr(col).ToString Then
                                Try
                                    itemXml.Add(New XElement(sourceTitle.Replace(" ", "_"), dr(col).ToString))
                                Catch ex As Exception
                                    itemXml.Add(New XElement("FailedColumnTitle", "FailedValue"))
                                End Try
                                debug.Append("Setting value.").AppendLine()
                                item(Me._destinationSpList.Fields(sourceTitle).InternalName) = dr(col).ToString
                                If Not isNew Then fieldsUpdated += 1
                            End If
                        End If

                    End If
                Next
                If containsUniqueField Then
                    If newFieldIds.Contains(item.TryGetValue(Of String)("UniqueField")) Then
                        Log.AddHistory(LogLevel.Warning, String.Format("Multiple results returned for UniqueField '{0}'.", item.TryGetValue(Of String)("UniqueField")))
                    End If
                    newFieldIds.Add(item.TryGetValue(Of String)("UniqueField"))
                End If
                If Me.IsTestMode Then
                    debug.Append("Setting title to GUID because in TEST MODE.").AppendLine()
                    item(SPBuiltInFieldId.Title) = Guid.NewGuid.ToString
                    If containsUniqueField Then
                        debug.Append("Setting 'UniqueField' to GUID because in TEST MODE.").AppendLine()
                        item("UniqueField") = Guid.NewGuid.ToString
                    End If
                End If
                If itemXml.Elements.Count > 0 Then
                    debug.Append("Updating (saving) item.")
                    web.AllowUnsafeUpdates = True
                    item.Update()
                    web.AllowUnsafeUpdates = False
                    If isNew Then
                        itemsAdded += 1
                        Log.AddHistory(LogLevel.Verbose, String.Format("Added:{0}{1}", vbNewLine, itemXml.ToString))
                    Else
                        itemsUpdated += 1
                        Log.AddHistory(LogLevel.Verbose, String.Format("Updated:{0}{1}", vbNewLine, itemXml.ToString))
                    End If
                Else
                    Log.AddHistory(LogLevel.Verbose, String.Format("No changes to item '{0}'.", item.TryGetValue(Of String)(_destinationSpList.Fields.GetField("UniqueField").StaticName)))
                End If

                Log.AddHistory(LogLevel.VerboseDebug, debug.ToString)
            Catch ex As Exception
                Log.AddHistory(LogLevel.Critical, debug.ToString)
                Throw New Exception(ex.Message)
            End Try
        Next
        Log.AddHistory(LogLevel.Information, String.Format("Added {0} new items, updated {1} existing items, updated {2} total fields.", itemsAdded, itemsUpdated, fieldsUpdated))
    End Sub

    Private Function GetConnection() As IDbConnection
        Dim connection_string As String = Item("Connection String").ToString

        Dim cn As IDbConnection = Nothing

        Select Case Item("Provider Type").ToString.ToLower
            Case "oledb"
                cn = New OleDb.OleDbConnection(connection_string)
            Case "mysql"
                cn = New MySql.Data.MySqlClient.MySqlConnection(connection_string)
            Case "odbc"
                cn = New Odbc.OdbcConnection(connection_string)
            Case "mssql"
                cn = New SqlClient.SqlConnection(connection_string)
        End Select

        If cn Is Nothing Then
            Throw New Exception("Provider type was not found.")
        End If

        Return cn
    End Function

    Public Sub SetStatus(ByVal value As String)
        Me.SetStatus(value, String.Empty)
    End Sub

    Public Sub SetStatus(ByVal value As String, ByVal message As String)

        Dim mcv As New SPFieldMultiColumnValue(Item("Recurrence").ToString)
        mcv(8) = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt")
        Item("Recurrence") = mcv.ToString

        Dim urlValue As New SPFieldUrlValue
        urlValue.Url = String.Format("/lists/{0}/AllItems.aspx?FilterField1=Result%5Fx0020%5FId&FilterValue1={1}", ImportDataTimer.ImportDataTimerJob.HISTORY_LIST_TITLE, Me.Log.Id)
        urlValue.Description = value
        Item("Last Occurrence Result") = urlValue

        Item("Last Occurrence Message") = message

        Item.ParentList.ParentWeb.AllowUnsafeUpdates = True
        Item.Update()
        Item.ParentList.ParentWeb.AllowUnsafeUpdates = False

        Me.Log.AddHistory(LogLevel.Verbose, String.Format("Job status changed to '{0}' with message '{1}'.", value, message))
    End Sub

    Public Function GetFinalDuration() As String
        If _finalDuration Is Nothing Then
            Dim ts As TimeSpan = DateTime.Now - Me._startClock
            _finalDuration = String.Format("{0}m {1}s", (ts.Days * 24 * 60) + (ts.Hours * 60) + ts.Minutes, ts.Seconds)
        End If
        Return _finalDuration
    End Function

End Class
